if exists (select 1
          from sysobjects
          where  id = object_id('bd_proc_schedule_and_apply_summary')
          and type in ('P','PC'))
   drop procedure bd_proc_schedule_and_apply_summary
go


create procedure bd_proc_schedule_and_apply_summary (@date_begin DATE,@date_end DATE,@depts varchar(max) = '') as
BEGIN
  --排班及申请流程数据统计（基于排班明细表、加班明细表、请假明细表）
	--思路：1、创建返回临时表#returnTable
				--2、将depts分解插入#returnTable
				--3、将参与统计的数据放入#schedule_detail、#ot_detail、#leave_detail
				--4、分别按部门分组加不同的条件，统计出指定数据 LEFT JOIN 全部查询语句，部门id关联，将指定数据更新到#returnTable
				--5、返回#returnTable；


	--1、创建返回临时表#returnTable
	-- 	部门	depName
	-- 	标准工时	normalHours
	-- 	加班工时	otHours
	-- 	放班工时	fbHours
	-- 	加班工时	practicalOtHours
	-- 	请假工时	leaveHours
	-- 	早早班	zaozaoban
	-- 	早班	zaoban
	-- 	中班	zhongban
	-- 	日8进8出	day8
	-- 	夜班	yeban
	-- 	夜8进8出	night8
	-- 	早中	zaozhong
	-- 	中夜	zhongye
 	create table #returnTable(
		dep_serial bigint,
		dep_no varchar(20),
		dep_name varchar(200) COLLATE Chinese_PRC_CI_AS,
		normal_hours DECIMAL(18,2),
		ot_hours DECIMAL(18,2),
		fb_hours DECIMAL(18,2),
		practical_ot_hours DECIMAL(18,2),
		leave_hours DECIMAL(18,2),
		zaozaoban int,
		zaoban int,
		zhongban int,
		day8 int,
		yeban int,
		night8 int,
		zaozhong int,
		zhongye int
	)

	--2、将depts分解插入#returnTable
	insert into #returnTable(dep_serial) select * from string_split(@depts,',');

	--3将参与统计的数据放入#schedule_detail、#ot_detail、#leave_detail
	--3.1#schedule_detail
	SELECT d.* into #schedule_detail  FROM  bd_kq_schedule_detail d
	INNER JOIN dt_user u on u.user_serial = d.user_serial
	WHERE u.user_dep in (select dep_serial from #returnTable) and d.shift_date BETWEEN @date_begin and @date_end

	--3.2#ot_detail
	select j.* into #ot_detail from lr_jiaba j
	INNER JOIN bd_over_time_order o on o.order_id = j.order_id
	where o.ot_date BETWEEN @date_begin and @date_end and o.status !=2 and o.is_delete = 0

	--3.3#leave_detail
	select k.* into #leave_detail from lr_kqingj k
	INNER JOIN bd_leave_order o on o.order_id = k.order_id
	where k.kssj BETWEEN @date_begin and @date_end and o.status !=2 and o.is_del = 0 and whether_validity = 1

	--4、分别按部门分组加不同的条件，统计出指定数据 LEFT JOIN 全部查询语句，部门id关联，将指定数据更新到#returnTable

	update r
		set
				dep_name = d.dep_name,
				dep_no = d.dep_no,
				normal_hours = isnull(a1.normal_hours,0),
				ot_hours = isnull(a2.ot_hours,0)+ isnull(a3.ot_hours,0)+ isnull(a4.ot_hours,0),
				fb_hours = isnull(a5.fb_hours,0),
				practical_ot_hours = isnull(a6.practical_ot_hours,0),
				leave_hours = isnull(a7.leave_hours,0),
				zaozaoban = isnull(a8.zaozaoban,0),
				zaoban  = isnull(a9.zaoban,0),
				zhongban = isnull(a10.zhongban,0),
				day8 = isnull(a11.day8,0),
				yeban = isnull(a12.yeban,0),
				night8 = isnull(a13.night8,0),
				zaozhong = isnull(a14.zaozhong,0),
				zhongye = isnull(a15.zhongye,0)
	from #returnTable r
	inner join dt_dep d on d.dep_serial = r.dep_serial

	--4.1 标准工时
	LEFT JOIN(
	select x. user_dep,sum(isnull(x.allt,0))/60.0 as normal_hours  from (
		select u.user_dep,s.allt  from #schedule_detail d
		INNER JOIN zt_banci b on b.id = d.shift_id
		INNER JOIN zt_shid s on b.bh = s.parent
		inner join dt_user u on d.user_serial = u.user_serial
		inner join bd_kq_work_calendar_detail c on u.user_work_calendar = c.parent_id and d.shift_date = c.work_date
		where s.lx = 0 and c.is_weekend = 0  and c.is_statutory = 0
	) x group by x. user_dep) a1 on a1.user_dep = r.dep_serial

	--4.2 加班工时(班次上的加班，统计全部，不论有无加班单)
	--4.2.1 平时加班
	LEFT JOIN(
	select x. user_dep,sum(isnull(x.allt,0))/60.0 as ot_hours  from (
		select u.user_dep,(s.xbsj-s.sbsj) as allt  from #schedule_detail d
		INNER JOIN zt_banci b on b.id = d.shift_id
		INNER JOIN zt_shid s on b.bh = s.parent
		inner join dt_user u on d.user_serial = u.user_serial
		inner join bd_kq_work_calendar_detail c on u.user_work_calendar = c.parent_id and d.shift_date = c.work_date
		where s.lx = 1 and c.is_weekend = 0  and c.is_statutory = 0
	) x group by x. user_dep) a2 on a2.user_dep = r.dep_serial

	--4.2.2 非工作日加班(上下班时段)
	LEFT JOIN(
	select x. user_dep,sum(isnull(x.allt,0))/60.0 as ot_hours from (
		select u.user_dep,s.allt  from #schedule_detail d
		INNER JOIN zt_banci b on b.id = d.shift_id
		INNER JOIN zt_shid s on b.bh = s.parent
		inner join dt_user u on d.user_serial = u.user_serial
		inner join bd_kq_work_calendar_detail c on u.user_work_calendar = c.parent_id and d.shift_date = c.work_date
		where s.lx = 0 and (c.is_weekend = 1  or c.is_statutory = 1)
	) x group by x. user_dep) a3 on a3.user_dep = r.dep_serial

	--4.2.3 非工作日加班(加班时段)
	LEFT JOIN(
	select x. user_dep,sum(isnull(x.allt,0))/60.0 as ot_hours  from (
		select u.user_dep,(s.xbsj-s.sbsj) as allt  from #schedule_detail d
		INNER JOIN zt_banci b on b.id = d.shift_id
		INNER JOIN zt_shid s on b.bh = s.parent
		inner join dt_user u on d.user_serial = u.user_serial
		inner join bd_kq_work_calendar_detail c on u.user_work_calendar = c.parent_id and d.shift_date = c.work_date
		where s.lx = 1 and (c.is_weekend = 1  or c.is_statutory = 1)
	) x group by x. user_dep) a4 on a4.user_dep = r.dep_serial

	--4.3 放班工时
	LEFT JOIN(
	select x.user_dep,sum(isnull(x.jsjg,0))/60.0 as fb_hours  from (
		select u.user_dep,d.jsjg  from #leave_detail d
		INNER JOIN dt_user u on d.user_serial = u.user_serial
	where d.bh = '10003'
	) x group by x.user_dep) a5 on a5.user_dep = r.dep_serial

	--4.4 加班工时（实际有效的加班单（排除删除和驳回的数据））
	LEFT JOIN(
	select x.user_dep,sum(isnull(x.jsjg,0))/60.0 as practical_ot_hours  from (
		select  u.user_dep,d.jsjg from #ot_detail d
		INNER JOIN dt_user u on d.user_serial = u.user_serial
	) x group by x.user_dep) a6 on a6.user_dep = r.dep_serial

	--4.5 请假工时
	LEFT JOIN(
	select x.user_dep,sum(isnull(x.jsjg,0))/60.0 as leave_hours  from (
		select u.user_dep,d.jsjg  from #leave_detail d
		INNER JOIN dt_user u on d.user_serial = u.user_serial
		where d.bh != '10003'
	) x group by x.user_dep) a7 on a7.user_dep = r.dep_serial

	--4.6 早早班
	LEFT JOIN(
	select x.user_dep,count(1) as	zaozaoban from (
		select  u.user_dep,d.shift_id  from 	#schedule_detail d
		INNER JOIN dt_user u on d.user_serial = u.user_serial
		where d.shift_id in (select  id from  zt_banci where shift_type = 50000 )
	) x GROUP BY x.user_dep) a8 on a8.user_dep = r.dep_serial

	--4.7 早班
	LEFT JOIN(
	select x.user_dep,count(1) as	zaoban from (
		select  u.user_dep,d.shift_id  from 	#schedule_detail d
		INNER JOIN dt_user u on d.user_serial = u.user_serial
		where d.shift_id in (select  id from  zt_banci where shift_type = 50001 )
	) x GROUP BY x.user_dep) a9 on a9.user_dep = r.dep_serial

	--4.8 中班
	LEFT JOIN(
	select x.user_dep,count(1) as	zhongban from (
		select  u.user_dep,d.shift_id  from 	#schedule_detail d
		INNER JOIN dt_user u on d.user_serial = u.user_serial
		where d.shift_id in (select  id from  zt_banci where shift_type = 50002 )
	) x GROUP BY x.user_dep) a10 on a10.user_dep = r.dep_serial

	--4.9 日8进8出
	LEFT JOIN(
	select x.user_dep,count(1) as	day8 from (
		select  u.user_dep,d.shift_id  from 	#schedule_detail d
		INNER JOIN dt_user u on d.user_serial = u.user_serial
		where d.shift_id in (select  id from  zt_banci where shift_type = 50003 )
	) x GROUP BY x.user_dep) a11 on a11.user_dep = r.dep_serial

	--4.10 夜班
	LEFT JOIN(
	select x.user_dep,count(1) as	yeban from (
		select  u.user_dep,d.shift_id  from 	#schedule_detail d
		INNER JOIN dt_user u on d.user_serial = u.user_serial
		where d.shift_id in (select  id from  zt_banci where shift_type = 50004 )
	) x GROUP BY x.user_dep) a12 on a12.user_dep = r.dep_serial

	--4.11 夜8进8出
	LEFT JOIN(
	select x.user_dep,count(1) as	night8 from (
		select  u.user_dep,d.shift_id  from 	#schedule_detail d
		INNER JOIN dt_user u on d.user_serial = u.user_serial
		where d.shift_id in (select  id from  zt_banci where shift_type = 50005 )
	) x GROUP BY x.user_dep) a13 on a13.user_dep = r.dep_serial

	--4.12 早中
	LEFT JOIN(
	select x.user_dep,count(1) as	zaozhong from (
		select  u.user_dep,d.shift_id  from 	#schedule_detail d
		INNER JOIN dt_user u on d.user_serial = u.user_serial
		where d.shift_id in (select  id from  zt_banci where shift_type = 50006 )
	) x GROUP BY x.user_dep) a14 on a14.user_dep = r.dep_serial

	--4.13 中夜
	LEFT JOIN(
	select x.user_dep,count(1) as	zhongye from (
		select  u.user_dep,d.shift_id  from 	#schedule_detail d
		INNER JOIN dt_user u on d.user_serial = u.user_serial
		where d.shift_id in (select  id from  zt_banci where shift_type = 50007 )
	) x GROUP BY x.user_dep) a15 on a15.user_dep = r.dep_serial


	--5、返回#returnTable；
	select * from #returnTable;
END
go
